<?php
class ModelSaleReturn extends Model {
	public function addReturn($data) {
		$data['date_added'] = $GLOBALS['V5_NOW'];
		$data['date_modified'] = $GLOBALS['V5_NOW'];
		return $this->db->insert('return', $data);
	}

	public function editReturn($return_id, $data) {
		$data['date_modified'] = $GLOBALS['V5_NOW'];
		$this->db->update('return', $data, array('return_id' => $return_id));
	}

	public function editReturnAction($return_id, $return_action_id) {
		$this->db->query("UPDATE `@@return` SET return_action_id = " . (int)$return_action_id . " WHERE return_id = " . (int)$return_id);
	}

	public function deleteReturn($return_id) {
		$this->db->query("DELETE FROM `@@return` WHERE return_id = " . (int)$return_id);
		$this->db->query("DELETE FROM  @@return_history WHERE return_id = " . (int)$return_id);
	}

	public function getReturn($return_id) {
		$query = $this->db->query("SELECT DISTINCT *, (SELECT CONCAT(c.firstname, ' ', c.lastname) FROM  @@customer c WHERE c.customer_id = r.customer_id) AS customer FROM `@@return` r WHERE r.return_id = " . (int)$return_id);

		return $query->row;
	}

	public function getReturns($filter = null) {
		$sql = "SELECT *, CONCAT(r.firstname, ' ', r.lastname) AS customer, (SELECT rs.name FROM  @@return_status rs WHERE rs.return_status_id = r.return_status_id AND rs.language_id = " . (int)C('config_language_id') . ") AS status FROM `@@return` r WHERE 1";

		if ($filter->get('filter_return_id')) {
			$sql .= "r.return_id = " . (int)$filter->get('filter_return_id');
		}

		if ($filter->get('filter_order_id')) {
			$sql .= "r.order_id = " . (int)$filter->get('filter_order_id');
		}

		if ($filter->get('filter_customer')) {
			$sql .= "CONCAT(r.firstname, ' ', r.lastname) LIKE '" . ES($filter->get('filter_customer')) . "%'";
		}

		if ($filter->get('filter_product')) {
			$sql .= "r.product = '" . ES($filter->get('filter_product')) . "'";
		}

		if ($filter->get('filter_model')) {
			$sql .= "r.model = '" . ES($filter->get('filter_model')) . "'";
		}

		if ($filter->get('filter_return_status_id')) {
			$sql .= "r.return_status_id = " . (int)$filter->get('filter_return_status_id');
		}

		if ($filter->get('filter_date_added')) {
			$sql .= "DATE(r.date_added) = DATE('" . ES($filter->get('filter_date_added')) . "')";
		}

		if ($filter->get('filter_date_modified')) {
			$sql .= "DATE(r.date_modified) = '" . ES($filter->get('filter_date_modified')) . "'";
		}

		$sql .= $filter->sort('r.return_id', 'DESC');
		$sql .= $filter->limit();

		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function getTotalReturns($filter = null) {
      	$sql = "SELECT COUNT(*) AS total FROM `@@return` r WHERE 1";

	if ($filter->get('filter_return_id')) {
			$sql .= "r.return_id = " . (int)$filter->get('filter_return_id');
		}

		if ($filter->get('filter_order_id')) {
			$sql .= "r.order_id = " . (int)$filter->get('filter_order_id');
		}

		if ($filter->get('filter_customer')) {
			$sql .= "CONCAT(r.firstname, ' ', r.lastname) LIKE '" . ES($filter->get('filter_customer')) . "%'";
		}

		if ($filter->get('filter_product')) {
			$sql .= "r.product = '" . ES($filter->get('filter_product')) . "'";
		}

		if ($filter->get('filter_model')) {
			$sql .= "r.model = '" . ES($filter->get('filter_model')) . "'";
		}

		if ($filter->get('filter_return_status_id')) {
			$sql .= "r.return_status_id = " . (int)$filter->get('filter_return_status_id');
		}

		if ($filter->get('filter_date_added')) {
			$sql .= "DATE(r.date_added) = DATE('" . ES($filter->get('filter_date_added')) . "')";
		}

		if ($filter->get('filter_date_modified')) {
			$sql .= "DATE(r.date_modified) = '" . ES($filter->get('filter_date_modified')) . "'";
		}
		
		return $this->db->queryOne($sql);
	}

	public function getTotalReturnsByReturnStatusId($return_status_id) {
      	return $this->db->queryOne("SELECT COUNT(*) AS total FROM `@@return` WHERE return_status_id = " . (int)$return_status_id);
	}

	public function getTotalReturnsByReturnReasonId($return_reason_id) {
      	return $this->db->queryOne("SELECT COUNT(*) AS total FROM `@@return` WHERE return_reason_id = " . (int)$return_reason_id);
	}

	public function getTotalReturnsByReturnActionId($return_action_id) {
      	return $this->db->queryOne("SELECT COUNT(*) AS total FROM `@@return` WHERE return_action_id = " . (int)$return_action_id);
	}

	public function addReturnHistory($return_id, $data) {
		$this->db->query("UPDATE `@@return` SET return_status_id = " . (int)$data['return_status_id'] . ", date_modified = '{$GLOBALS['V5_NOW']}' WHERE return_id = " . (int)$return_id);
		
		$data['return_id'] = $return_id;
		$data['notify'] = isset($data['notify']) ? $data['notify'] : 0;
		$data['date_added'] = $GLOBALS['V5_NOW'];
		$this->db->insert('return_history', $data);
		
      	if ($data['notify']) {
        	$return = $this->db->queryOne("SELECT *, rs.name AS status FROM `@@return` r LEFT JOIN  @@return_status rs ON (r.return_status_id = rs.return_status_id) WHERE r.return_id = " . (int)$return_id . " AND rs.language_id = " . (int)C('config_language_id'));

			if ($return) {

				$params = array(
						'{return_id}' => $return_id,
						'{return_firstname}' => $return['firstname'],
						'{return_lastname}' => $return['lastname'],
						'{return_date_added}' => v5_date_format($return['date_added']),
						'{return_status}' => $return['status'],
						'{return_comment}' => strip_tags(html_entity_decode($data['comment'], ENT_QUOTES, 'UTF-8')),
				);

				send_mail($return['email'], 'order_return_update', $params);
			}
		}
	}

	public function getReturnHistories($return_id, $start = 0, $limit = 10) {
		if ($start < 0) $start = 0;
		if ($limit < 1) $limit = 10;

		$query = $this->db->query("SELECT rh.date_added, rs.name AS status, rh.comment, rh.notify FROM  @@return_history rh LEFT JOIN  @@return_status rs ON rh.return_status_id = rs.return_status_id WHERE rh.return_id = " . (int)$return_id . " AND rs.language_id = " . (int)C('config_language_id') . " ORDER BY rh.date_added ASC LIMIT " . (int)$start . "," . (int)$limit);

		return $query->rows;
	}

	public function getTotalReturnHistories($return_id) {
	  	return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@return_history WHERE return_id = " . (int)$return_id);
	}

	public function getTotalReturnHistoriesByReturnStatusId($return_status_id) {
	  	return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@return_history WHERE return_status_id = " . (int)$return_status_id . " GROUP BY return_id");
	}
}
?>